insert overwrite table jms_dm.dm_customer_business_summary_dt
select
     to_date(way.input_time) as date_time
    ,cust.customer_id
    ,cust.customer_code
    ,replace(cust.name, '拼多多', '桃花岛') as customer_name
    ,dim.agent_code
    ,dim.agent_name
    ,dim.fran_code
    ,dim.fran_name
    ,dim.code
    ,dim.name
    ,count(1) as cnt
    ,dim.provider_id
    ,dim.provider_desc
    ,dim.city_id
    ,dim.city_desc
    ,dim.area_id
    ,dim.area_desc
    ,cust.attribute_id
    ,cust.customer_create_time
    ,addit.customer_category
    ,way.order_source_code
    ,way.order_source_name
    ,nvl(cust.type_id, '38') as type_id
    ,case when cust.type_id = '36' then '商务件'
          when cust.type_id = '37' then '电商件'
          else '个人件' end as type_name
    ,null as weight
    ,to_date(input_time) as dt
from (
    select
         waybill_no
        ,customer_code
        ,pick_network_code
        ,pick_network_name
        ,ordersource_code as order_source_code
        ,nvl(ordersource_name, '其他') as order_source_name
        ,input_time
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt between '{{ execution_date | cst_ds }}' and '{{ execution_date | cst_ds }}'
    and nvl(is_void,0)  =  0
) way
left join (
    select
         id as customer_id
        ,code as customer_code
        ,attribute_id
        ,name
        ,create_time as customer_create_time
        ,type_id
    from jms_dim.dim_lmdm_sys_customer
) cust on cust.customer_code = way.customer_code
inner join (
    select
         agent_code
        ,agent_name
        ,fran_code
        ,fran_name
        ,provider_id
        ,provider_desc
        ,city_id
        ,city_desc
        ,area_id
        ,area_desc
        ,code
        ,name
    from jms_dim.dim_network_whole_massage
) dim on way.pick_network_code = dim.code
left join (
    select
         code
        ,customer_category
    from jms_dim.dim_lmdm_sys_customer_addition
) addit on cust.customer_code = addit.code
where instr(dim.agent_code, 'test') = 0
  and instr(dim.agent_code, '3021888') = 0
  and instr(dim.agent_code, '888888') = 0
  and instr(dim.code, '888888') = 0
  and instr(nvl(cust.name, '散客'), '测试') = 0
group by to_date(way.input_time)
        ,dim.agent_code
        ,dim.agent_name
        ,dim.fran_code
        ,dim.fran_name
        ,dim.code
        ,dim.name
        ,cust.customer_id
        ,cust.customer_code
        ,replace(cust.name, '拼多多', '桃花岛')
        ,attribute_id
        ,dim.provider_id
        ,dim.provider_desc
        ,dim.city_id
        ,dim.city_desc
        ,dim.area_id
        ,dim.area_desc
        ,cust.customer_create_time
        ,addit.customer_category
        ,way.order_source_code
        ,way.order_source_name
        ,nvl(cust.type_id, '38')
        ,case when cust.type_id = '36' then '商务件'
              when cust.type_id = '37' then '电商件'
              else '个人件' end
        ,to_date(input_time)
distribute by dt,pmod(hash(rand()),30);
